package com.cctcq.dao.impl;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.cctcq.dao.DaoException;
import com.cctcq.dao.DaoTemplate;
import com.cctcq.dao.RowMapper;
import com.cctcq.domain.TourGroup;
import com.cctcq.util.jdbc.JdbcUtils;
import com.cctcq.util.jdbc.Page;
import com.cctcq.util.jdbc.PageUtil;
import com.cctcq.util.jdbc.Result;
import com.cctcq.util.jdbc.SearchQuery;

public class TourGroupDaoImpl extends DaoTemplate{
	DaoTemplate daoTemplate = new DaoTemplate();
	//log4j日志实例
	static Logger logger = Logger.getLogger (TourGroupDaoImpl.class) ;
	public Result getTourGroups(Page page, SearchQuery sq) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		TourGroup tourGroup = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.TourGroupName, tab2.DepartDate, tab2.MarketCharge, tab2.TourGroupId, j.PictureURL, ISNULL(SUM(tab3.Incidentals), 0) Incidentals, SUM ( tab2.ExpectAccountNumber - isnull(tab4.num, 0)) Surplus FROM ( SELECT b.productid, b.productname, b.departplace, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.TourGroupName, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa";
			List<Object> list = new ArrayList<Object>();
			if (sq != null) {
				sql += " INNER JOIN ProductFee bb ON aa.productid = bb.productid" ;
				if (sq.getProductId() != null && !sq.getProductId().trim().equals("")) {
					sql += " WHERE aa.productid = ?";
					list.add(sq.getProductId().trim());
				}
				sql += "UNION SELECT aa.ProductId, aa.productname, aa.departplace, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid ";
				if (sq.getProductId() != null && !sq.getProductId().trim().equals("")) {
					sql += " WHERE aa.Available = '1' AND aa.PubState = '发布' and bb.DepartDate > getdate() AND aa.ServiceRank = '2' AND aa.productid = ? ) b";
					list.add(sq.getProductId().trim());
				}
				sql +=" INNER JOIN (SELECT ProductId, AreaId FROM Destination UNION SELECT ProductId, MacroDestinationId AreaId FROM Product) c ON b.ProductId = c.ProductId INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE b.SlowLookType = '闲读' AND e.tourgroupchargetype = '成人' AND e.IsDefaultPrice = 1) tab2 LEFT JOIN ( SELECT f.TourGroupId, SUM (marketcharge) Incidentals FROM TourGroupCharge f WHERE f.tourgroupchargetype NOT IN ('老人', '成人', '儿童') GROUP BY f.TourGroupId ) tab3 ON tab2.TourGroupId = tab3.TourGroupId LEFT JOIN ( SELECT g.TourGroupId, isnull(SUM(g.num), 0) num FROM PredefinedGroupOrder g WHERE g.OrderState IN ('报名', '留位') GROUP BY g.TourGroupId ) tab4 ON tab2.TourGroupId = tab4.TourGroupId LEFT JOIN Route h ON tab2.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId WHERE i.IsDefaultPicture = 1 GROUP BY tab2.TourGroupName, tab2.DepartDate, tab2.MarketCharge, tab2.TourGroupId, j.PictureURL";
			}
			System.out.println(sql);
			String sq1 = "SELECT COUNT (*) FROM ("+sql+") x";
			System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> tourGroups = new ArrayList<Object>();
			while (rs.next()) {
				tourGroup = MappingTourGroup(rs);
				tourGroups.add(tourGroup);
			}
			Result r = new Result();
			r.setContent(tourGroups);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	
	public Result getTihuiTourGroups(Page page, SearchQuery sq) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		TourGroup tourGroup = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.TourGroupName, tab2.DepartDate, tab2.MarketCharge, tab2.TourGroupId, j.PictureURL, ISNULL(SUM(tab3.Incidentals), 0) Incidentals, SUM ( tab2.ExpectAccountNumber - isnull(tab4.num, 0)) Surplus FROM ( SELECT b.productid, b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.TourGroupName, d.ExpectAccountNumber FROM ( SELECT aa.ProductId, aa.productname, aa.departplace, aa.MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa";
			List<Object> list = new ArrayList<Object>();
			if (sq != null) {
				sql += " INNER JOIN ProductFee bb ON aa.productid = bb.productid" ;
				if (sq.getProductId() != null && !sq.getProductId().trim().equals("")) {
					sql += " WHERE aa.productid = ?";
					list.add(sq.getProductId().trim());
				}
				sql += "UNION SELECT aa.ProductId, aa.productname, aa.departplace, aa.MacroDestination, aa.ServiceRank, aa.Feature, aa.OverSea, aa.ProductSlogan, aa.slowlooktype FROM Product aa INNER JOIN TourGroup bb ON aa.productid = bb.productid ";
				if (sq.getProductId() != null && !sq.getProductId().trim().equals("")) {
					sql += " WHERE aa.Available = '1' AND aa.PubState = '发布' and bb.DepartDate > getdate() AND aa.ServiceRank ='1' AND aa.productid = ? ) b";
					list.add(sq.getProductId().trim());
				}
				sql +=" INNER JOIN Destination c ON b.ProductId = c.ProductId INNER JOIN TourGroup d ON b.productid = d.productid RIGHT JOIN ( SELECT productid, (CreateDate) createdate FROM TourGroup ) tab1 ON b.productid = tab1.productid AND d.createdate = tab1.createdate INNER JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId WHERE b.SlowLookType = '品读' AND e.tourgroupchargetype = '成人' AND e.IsDefaultPrice = 1) tab2 LEFT JOIN ( SELECT f.TourGroupId, SUM (marketcharge) Incidentals FROM TourGroupCharge f WHERE f.tourgroupchargetype NOT IN ('老人', '成人', '儿童') GROUP BY f.TourGroupId ) tab3 ON tab2.TourGroupId = tab3.TourGroupId LEFT JOIN ( SELECT g.TourGroupId, isnull(SUM(g.num), 0) num FROM PredefinedGroupOrder g WHERE g.OrderState IN ('报名', '留位') GROUP BY g.TourGroupId ) tab4 ON tab2.TourGroupId = tab4.TourGroupId LEFT JOIN Route h ON tab2.productid = h.productid LEFT JOIN RouteScenerySpot i ON h.routeid = i.routeid LEFT JOIN ScenerySpot j ON j.ScenerySpotId = i.ScenerySpotId WHERE i.IsDefaultPicture = 1 GROUP BY tab2.TourGroupName, tab2.DepartDate, tab2.MarketCharge, tab2.TourGroupId, j.PictureURL";
			}
			//System.out.println(sql);
			String sq1 = "SELECT COUNT (*) FROM ("+sql+") x";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> tourGroups = new ArrayList<Object>();
			while (rs.next()) {
				tourGroup = MappingTourGroup(rs);
				tourGroups.add(tourGroup);
			}
			Result r = new Result();
			r.setContent(tourGroups);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	private TourGroup MappingTourGroup(ResultSet rs) throws SQLException {
		TourGroup tourGroup = new TourGroup();
		tourGroup.setTourGroupId(rs.getString(4));
		tourGroup.setTourGroupName(rs.getString(1));
		tourGroup.setDepartDate(rs.getString(2));
		tourGroup.setMeetDate(rs.getString(3));//价格
		//tourGroup.setBookTip(rs.getString(6));//杂费
		//tourGroup.setFeesAndTips(rs.getString(7));//可定人数
		return tourGroup;
	}
	
	
//	public Result getTourGroups2(Page page, SearchQuery sq) {
//		Connection conn = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		TourGroup tourGroup = null;
//		try {
//			conn = JdbcUtils.getConnection();
//			String sql = "select * from TourGroup a inner join product b on a.productid = b.productid";
//			String sq1 = "select count(*) from TourGroup a inner join product b on a.productid = b.productid";
//			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//			rs = ps.executeQuery();
//			rs.next();
//			int totalRecords  = rs.getInt(1);
//			page = PageUtil.createPage(page, totalRecords);
//			
//			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
//			rs = ps.executeQuery();
//			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
//			List<Object> tourGroups = new ArrayList<Object>();
//			while (rs.next()) {
//				tourGroup = MappingTourGroup(rs);
//				tourGroups.add(tourGroup);
//			}
//			Result r = new Result();
//			r.setContent(tourGroups);
//			r.setPage(page);
//			return r;
//		} catch (SQLException e) {
//			logger.error(e);
//			throw new DaoException(e.getMessage(), e); 
//		} finally {
//			JdbcUtils.free(rs, ps, conn);
//		}
//	}
	
	/*
	 * 根据团ID查询当季团
	 */
	public Result getgetTourGroupsIn(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		TourGroup tourGroup = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select a.TourGroupId ,a.TourGroupName from (select tab.* from (select productid from tourgroup ";
			List<Object> list = new ArrayList<Object>();
			if (sq != null) {
				if (sq.getTourGroupId() != null && !sq.getTourGroupId().trim().equals("")) {
					sql += " where tourgroupid =?) aa left join tourgroup tab on aa.productid = tab.productid) a left join ProductSeason b on a.productid = b.productid";
					list.add(new String(sq.getTourGroupId()));
				}
				if (sq.getTourGroupId() != null && !sq.getTourGroupId().trim().equals("")) {
					sql += " where a.DepartDate >= b.StartDate and a.DepartDate <= b.EndDate and a.Available = 1 and TourGroupid !=?" ;
					list.add(new String(sq.getTourGroupId()));
				}
			}
			//System.out.println(sql);
			String sq1 = sql.replace("select a.TourGroupId ,a.TourGroupName from", "select count(*) from");
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> tourGroups = new ArrayList<Object>();
			while (rs.next()) {
				tourGroup = MappingTourGroupIn(rs);
				tourGroups.add(tourGroup);
			}
			Result r = new Result();
			r.setContent(tourGroups);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	/*/
	 * 根据团ID查询订单编号
	 */
	public TourGroup getgetgetTourGroupsOrderCode(String tourGroupId) {
		String sql = "select * from tourGroup where tourGroupId = ?";
		Object[] args = new Object[] { tourGroupId};
		Object tourGroup = this.daoTemplate.find(sql, args, new RowMapper() {
			public Object mapRow(ResultSet rs) throws SQLException {
				TourGroup tourGroup = new TourGroup();
				tourGroup.setTourGroupCode(rs.getString(40));
				return tourGroup;
			}
		});
		return (TourGroup)tourGroup;
	}
	
	/*
	 * 根据团ID查询非当季团
	 */
	public Result getgetTourGroupsOut(Page page, SearchQuery sq) throws UnsupportedEncodingException {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		TourGroup tourGroup = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select a.TourGroupId ,a.TourGroupName from ( select tab.* from (select productid from tourgroup ";
			List<Object> list = new ArrayList<Object>();
			if (sq != null) {
				if (sq.getTourGroupId() != null && !sq.getTourGroupId().trim().equals("")) {
					sql += " where tourgroupid =?) aa left join tourgroup tab on aa.productid = tab.productid ) a left join ProductSeason b on a.productid = b.productid";
					list.add(new String(sq.getTourGroupId()));
				}
				if (sq.getTourGroupId() != null && !sq.getTourGroupId().trim().equals("")) {
					sql += " where (a.DepartDate < b.StartDate or a.DepartDate > b.EndDate )and a.Available = 1 and TourGroupid !=?" ;
					list.add(new String(sq.getTourGroupId()));
				}
			}
			//System.out.println(sql);
			String sq1 = sql.replace("select a.TourGroupId ,a.TourGroupName from", "select count(*) from");
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> tourGroups = new ArrayList<Object>();
			while (rs.next()) {
				tourGroup = MappingTourGroupIn(rs);
				tourGroups.add(tourGroup);
			}
			Result r = new Result();
			r.setContent(tourGroups);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
	private TourGroup MappingTourGroupIn(ResultSet rs) throws SQLException {
		TourGroup tourGroup = new TourGroup();
		tourGroup.setTourGroupId(rs.getString(1));
		tourGroup.setTourGroupName(rs.getString(2));
		return tourGroup;
	}
//	public TourGroup findTourGroup(String city) {
//		String sql = "select * from Area t1,Product t2,TourGroup t3,Destination t4 " +
//				"where t3.ProductId = t2.ProductId and t2.ProductId = t4.ProductId and t1.AreaId = t4.AreaId and t1.City = ?";
//		Object[] args = new Object[] { city };
//		Object tourGroup = this.daoTemplate.find(sql, args, new RowMapper() {
//			public Object mapRow(ResultSet rs) throws SQLException {
//				TourGroup tourGroup = new TourGroup();
//				tourGroup.setTourGroupId(rs.getString(1));
//				tourGroup.setTourGroupCode(rs.getString(2));
//				tourGroup.setProductId(rs.getString(3));
//				tourGroup.setDepartDate(rs.getString(4));
//				tourGroup.setMeetDate(rs.getString(5));
//				tourGroup.setExpireDate(rs.getString(6));
//				tourGroup.setExpectDays(rs.getString(7));
//				tourGroup.setTourGroupType(rs.getString(8));
//				tourGroup.setFeather(rs.getString(9));
//				tourGroup.setLeaderNumber(Integer.valueOf(rs.getInt(10)));
//				tourGroup.setLeaderRequest(rs.getString(11));
//				tourGroup.setExpectAccountNumber(Integer.valueOf(rs.getInt(12)));
//				tourGroup.setBeGroupAccountNumber(Integer.valueOf(rs.getInt(13)));
//				
//				tourGroup.setStateChangeReason(rs.getString(15));
//				tourGroup.setTourGroupState(rs.getString(16));
//				tourGroup.setTourGroupPoint(Integer.valueOf(rs.getInt(17)));
//				tourGroup.setPredefined(rs.getBoolean(18));
//				tourGroup.setBeGroupCheckState(rs.getString(19));
//				tourGroup.setCreateDate(rs.getTimestamp(20));
//				
//				tourGroup.setAvailable(rs.getBoolean(22));
//				return tourGroup;
//			}
//		});
//		return (TourGroup)tourGroup;
//	}
	
	public Result getTourGroupsById(Page page, SearchQuery sq) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		TourGroup tourGroup = null;
		try {
			conn = JdbcUtils.getConnection();
			String sql = "SELECT tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab2.DepartDate, datename(weekday, tab2.DepartDate) weekday, tab2.MarketCharge, tab2.TourGroupId, tab2.TourGroupName, ISNULL(SUM(tab3.Incidentals), 0) Incidentals, SUM ( tab2.ExpectAccountNumber - isnull(tab4.num, 0)) Surplus, CONVERT ( VARCHAR (100), dateadd( DAY, tab2.duration, tab2.DepartDate ), 23 ) returnday FROM (";
			List<Object> list = new ArrayList<Object>();
			if (sq != null) {
				sql+=" SELECT b.productname, b.departplace, CASE cc.arealevel WHEN 5 THEN cc.district WHEN 4 THEN cc.City WHEN 3 THEN cc.province WHEN 2 THEN cc.country WHEN 1 THEN cc.continent END MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, b.ProductName TourGroupName, d.ExpectAccountNumber, b.duration FROM TourGroup d LEFT JOIN Product b ON d.productid = b.productid LEFT JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId LEFT JOIN Area cc ON b.MacroDestinationId = cc.AreaId ";
				if (sq.getProductId() != null && !sq.getProductId().trim().equals("")) {
					sql += " WHERE b.ProductId = ? AND tourgroupchargetype = 1 ) tab2 LEFT JOIN ( SELECT f.TourGroupId, SUM (marketcharge) Incidentals FROM TourGroupCharge f WHERE f.tourgroupchargetype != 1 GROUP BY f.TourGroupId ) tab3 ON tab2.TourGroupId = tab3.TourGroupId LEFT JOIN ( SELECT g.TourGroupId, isnull(SUM(g.num), 0) num FROM PredefinedGroupOrder g WHERE g.OrderState IN ('报名', '留位') GROUP BY g.TourGroupId ) tab4 ON tab2.TourGroupId = tab4.TourGroupId GROUP BY tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab2.DepartDate, datename(weekday, tab2.DepartDate), tab2.MarketCharge, tab2.TourGroupId, tab2.TourGroupName, CONVERT ( VARCHAR (100), dateadd( DAY, tab2.duration, tab2.DepartDate ), 23 )";
					list.add(sq.getProductId().trim());
				}
			}
			//System.out.println(sql);
			String sq1 = "select count(*) from (" + sql + ") a";
			//System.out.println(sq1);
			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			rs = ps.executeQuery();
			rs.next();
			int totalRecords  = rs.getInt(1);
			page = PageUtil.createPage(page, totalRecords);
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			for (int i = 0; i < list.toArray().length; i++) {
				ps.setObject(i + 1, list.toArray()[i]);
			}
			
			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
			rs = ps.executeQuery();
			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
			List<Object> tourGroups = new ArrayList<Object>();
			while (rs.next()) {
				tourGroup = MappingTourGroupById(rs);
				tourGroups.add(tourGroup);
			}
			Result r = new Result();
			r.setContent(tourGroups);
			r.setPage(page);
			return r;
		} catch (SQLException e) {
			logger.error(e);
			throw new DaoException(e.getMessage(), e); 
		} finally {
			JdbcUtils.free(rs, ps, conn);
		}
	}
//	/*
//	 * 根据团ID查询对应团数据
//	 */
//	public Result getTourGroupByTourGroupId(Page page, SearchQuery sq) {
//		Connection conn = null;
//		PreparedStatement ps = null;
//		ResultSet rs = null;
//		TourGroup tourGroup = null;
//		try {
//			conn = JdbcUtils.getConnection();
//			String sql = "SELECT tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab2.DepartDate, datename(weekday, tab2.DepartDate) weekday, tab2.MarketCharge, tab2.TourGroupId, tab2.TourGroupName, ISNULL(SUM(tab3.Incidentals), 0) Incidentals, SUM ( tab2.ExpectAccountNumber - isnull(tab4.num, 0)) Surplus, CONVERT ( VARCHAR (100), dateadd( DAY, tab2.duration, tab2.DepartDate ), 23 ) returnday FROM (";
//			List<Object> list = new ArrayList<Object>();
//			if (sq != null) {
//				sql+=" SELECT b.productname, b.departplace, b.MacroDestination, b.ServiceRank, b.Feature, b.OverSea, b.ProductSlogan, d.DepartDate, e.MarketCharge, d.TourGroupId, d.TourGroupName, d.ExpectAccountNumber, b.duration FROM TourGroup d LEFT JOIN Product b ON d.productid = b.productid LEFT JOIN TourGroupCharge e ON d.TourGroupId = e.TourGroupId  ";
//				if (sq.getTourGroupId() != null && !sq.getTourGroupId().trim().equals("")) {
//					sql += " WHERE d.TrouGroupId=? AND tourgroupchargetype = '成人' ) tab2 LEFT JOIN ( SELECT f.TourGroupId, SUM (marketcharge) Incidentals FROM TourGroupCharge f WHERE f.tourgroupchargetype NOT IN ('老人', '成人', '儿童') GROUP BY f.TourGroupId ) tab3 ON tab2.TourGroupId = tab3.TourGroupId LEFT JOIN ( SELECT g.TourGroupId, isnull(SUM(g.num), 0) num FROM PredefinedGroupOrder g WHERE g.OrderState IN ('报名', '留位') GROUP BY g.TourGroupId ) tab4 ON tab2.TourGroupId = tab4.TourGroupId GROUP BY tab2.productname, tab2.departplace, tab2.MacroDestination, tab2.ServiceRank, tab2.Feature, tab2.OverSea, tab2.ProductSlogan, tab2.DepartDate, datename(weekday, tab2.DepartDate), tab2.MarketCharge, tab2.TourGroupId, tab2.TourGroupName, CONVERT ( VARCHAR (100), dateadd( DAY, tab2.duration, tab2.DepartDate ), 23 )";
//					list.add(sq.getTourGroupId().trim());
//				}
//			}
//			System.out.println(sql);
//			String sq1 = "select count(*) from (" + sql + ") a";
//			System.out.println(sq1);
//			ps = conn.prepareStatement(sq1, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//			for (int i = 0; i < list.toArray().length; i++) {
//				ps.setObject(i + 1, list.toArray()[i]);
//			}
//			rs = ps.executeQuery();
//			rs.next();
//			int totalRecords  = rs.getInt(1);
//			page = PageUtil.createPage(page, totalRecords);
//			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//			for (int i = 0; i < list.toArray().length; i++) {
//				ps.setObject(i + 1, list.toArray()[i]);
//			}
//			
//			ps.setMaxRows(page.getEveryPage() * page.getCurrentPage());
//			rs = ps.executeQuery();
//			rs.relative(page.getEveryPage() * (page.getCurrentPage()-1));
//			List<Object> tourGroups = new ArrayList<Object>();
//			while (rs.next()) {
//				tourGroup = MappingTourGroupById(rs);
//				tourGroups.add(tourGroup);
//			}
//			Result r = new Result();
//			r.setContent(tourGroups);
//			r.setPage(page);
//			return r;
//		} catch (SQLException e) {
//			logger.error(e);
//			throw new DaoException(e.getMessage(), e); 
//		} finally {
//			JdbcUtils.free(rs, ps, conn);
//		}
//	}
	
	private TourGroup MappingTourGroupById(ResultSet rs) throws SQLException {
		TourGroup tourGroup = new TourGroup();
		tourGroup.setTourGroupName(rs.getString(12));
		tourGroup.setDepartDate(rs.getString(8));//该团出发时间
		tourGroup.setMeetDate(rs.getString(9));//该团出发星期几
		tourGroup.setLeaderNumber(rs.getInt(10));//米米
		tourGroup.setSalerId(rs.getString(14));//可订人数
		tourGroup.setTourGroupId(rs.getString(11));
		tourGroup.setExpectDays(rs.getString(15));//该团回来时间
		return tourGroup;
	}
}
